<#assign StringUtils = beansWrapperFn.getStaticModels()["org.apache.commons.lang3.StringUtils"]>
WITH w_site AS
  (SELECT t.*, row_number() over(order by t.code) rn ,
    project_DICTIONARY.code as projectCode ,
    project_DICTIONARY.name as projectName 
  FROM C1_SITE T
  left join C1_DICTIONARY project_DICTIONARY on (project_DICTIONARY.id = t.project_ID) 
where (NOW() BETWEEN IFNULL(T.BEGIN_DATE, NOW()) AND IFNULL(T.END_DATE, NOW()))
<#if StringUtils.isNotBlank(projectCode)>
and (instr(project_DICTIONARY.code, :projectCode) > 0 or instr(project_DICTIONARY.name, :projectCode) > 0)
</#if>
<#if StringUtils.isNotBlank(code)>
and (instr(t.code, :code) > 0 or instr(t.name, :code) > 0)
</#if>
  )
SELECT T.BEGIN_DATE AS beginDate ,
    T.CODE             AS code ,
    T.END_DATE         AS endDate ,
    T.ID               AS id ,
    T.NAME             AS name ,
    T.PROJECT_ID       AS projectId ,
    T.STYLE_ID         AS styleId ,
    style_DICTIONARY.code as styleCode ,
    style_DICTIONARY.name as styleName,
    T.projectCode ,
    T.projectName 
FROM w_site t
left join C1_DICTIONARY style_DICTIONARY on (style_DICTIONARY.id = t.STYLE_ID) 
WHERE t.rn BETWEEN :begin_row_num AND :end_row_num